
package model.dao;
import model.entities.Bill;
import model.entities.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import model.dao.service.UserDAOService;
import util.MD5;


public class UserDAO implements UserDAOService {
    private static UserDAO userDAO;
    
    public static UserDAO getInstance(){
        if(userDAO == null){
        userDAO = new UserDAO();
        }
        return userDAO;
    
    }
    
    @Override
    public List<User> getUsersList(){
       ArrayList<User> listUser = new ArrayList<User>(); 
       Connection con = ConnectionFactory.getConnection();
       String sql = "select * from tblUser  where IsActived ='true'";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User use = new User();
                use.setUserID(rs.getInt("UserID"));
                use.setUserName(rs.getString("UserName"));
                use.setPassword(rs.getString("Password"));
                use.setRole(rs.getBoolean("Role"));
                use.setName(rs.getString("Name"));
                use.setIdentityCard(rs.getString("IdentityCard"));
                use.setGender(rs.getBoolean("Gender"));
                use.setBirthday(rs.getDate("Birthday"));
                use.setPhone(rs.getString("Phone"));
                use.setEmail(rs.getString("Email"));
                use.setAddress(rs.getString("Address"));
                use.setNotes(rs.getString("Notes"));
                listUser.add(use);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return listUser;
    }
    
    @Override
    public User getUserByID(int userID){
        User use = new User();
        Connection con = ConnectionFactory.getConnection();
        String sql = "select * from tblUser where UserID = ? and IsActived ='true'";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            use.setUserID(rs.getInt("UserID"));
            use.setUserName(rs.getString("UserName"));
            use.setPassword(rs.getString("Password"));
            use.setRole(rs.getBoolean("Role"));
            use.setName(rs.getString("Name"));
            use.setIdentityCard(rs.getString("IdentityCard"));
            use.setGender(rs.getBoolean("Gender"));
            use.setBirthday(rs.getDate("Birthday"));
            use.setPhone(rs.getString("Phone"));
            use.setEmail(rs.getString("Email"));
            use.setAddress(rs.getString("Address"));
            use.setNotes(rs.getString("Notes"));
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return use;
    }    
    
    //CREATE
    @Override
    public boolean insertUser(model.entities.User user){
        String query = "INSERT INTO tblUser VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = model.dao.ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, user.getUserName());
            String passMD5 = MD5.getMd5(user.getPassword());
            pstmt.setString(2, passMD5);
            pstmt.setBoolean(3, user.isRole());
            pstmt.setString(4, user.getName());
            pstmt.setString(5, user.getIdentityCard());
            pstmt.setBoolean(6, user.isGender());
            pstmt.setDate(7, (Date) user.getBirthday());
            pstmt.setString(8, user.getPhone());
            pstmt.setString(9, user.getEmail());
            pstmt.setString(10, user.getAddress());
            pstmt.setString(11, user.getNotes());
            pstmt.setBoolean(12, true);
            return pstmt.executeUpdate() == 1;
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    //UPDATE
    @Override
    public boolean updateUser(model.entities.User user){
        String query = "UPDATE tblUser SET UserName=?, Password=?,Role=?, Name=?, IdentityCard=?,"
                        + "Gender=?,Birthday=?,Phone=?,Email=?,Address=?,Notes=?, IsActived=? WHERE UserID=?";
        Connection conn = model.dao.ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, user.getUserName());
            pstmt.setString(2, user.getPassword());
            pstmt.setBoolean(3, user.isRole());
            pstmt.setString(4, user.getName());
            pstmt.setString(5, user.getIdentityCard());
            pstmt.setBoolean(6, user.isGender());
            pstmt.setDate(7, (Date) user.getBirthday());
            pstmt.setString(8, user.getPhone());
            pstmt.setString(9, user.getEmail());
            pstmt.setString(10, user.getAddress());
            pstmt.setString(11, user.getNotes());
            pstmt.setBoolean(12, true);
            pstmt.setInt(13, user.getUserID());
            return pstmt.executeUpdate()==1;
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    //DELETE
    @Override
    public boolean deleteUser(int userID){
        String query = "UPDATE tblUser SET IsActived='false' WHERE UserID=?";
        Connection conn = model.dao.ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, userID);
            return pstmt.executeUpdate()==1;
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    //SEARCH BY ID
    @Override
    public List<User> findUserByID(int id){
        List<User> lstUser = new ArrayList<User>();
        String query = "SELECT * FROM tblUser WHERE UserID=? AND IsActived='true'";
        Connection conn = ConnectionFactory.getConnection();
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User u = new User();
                u.setUserID(rs.getInt("UserID"));
                u.setUserName(rs.getString("UserName"));
                u.setPassword(rs.getString("Password"));
                u.setRole(rs.getBoolean("Role"));
                u.setName(rs.getNString("Name"));
                u.setIdentityCard(rs.getString("IdentityCard"));
                u.setGender(rs.getBoolean("Gender"));
                u.setBirthday(rs.getDate("Birthday"));
                u.setPhone(rs.getString("Phone"));
                u.setEmail(rs.getString("Email"));
                u.setAddress(rs.getNString("Address"));
                u.setNotes(rs.getNString("Notes"));
                u.setIsActived(rs.getBoolean("IsActived"));
                lstUser.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return lstUser;
    }
    
    //SEARCH BY NAME
    @Override
    public List<User> findUserByName(String name){
        List<User> lstUser = new ArrayList<User>();
        String query = "SELECT * FROM tblUser WHERE Name=? AND IsActived='true'";
        Connection conn = ConnectionFactory.getConnection();
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User u = new User();
                u.setUserID(rs.getInt("UserID"));
                u.setUserName(rs.getString("UserName"));
                u.setPassword(rs.getString("Password"));
                u.setRole(rs.getBoolean("Role"));
                u.setName(rs.getNString("Name"));
                u.setIdentityCard(rs.getString("IdentityCard"));
                u.setGender(rs.getBoolean("Gender"));
                u.setBirthday(rs.getDate("Birthday"));
                u.setPhone(rs.getString("Phone"));
                u.setEmail(rs.getString("Email"));
                u.setAddress(rs.getNString("Address"));
                u.setNotes(rs.getNString("Notes"));
                u.setIsActived(rs.getBoolean("IsActived"));
                lstUser.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return lstUser;
    }

    @Override
    public boolean login(String userName, String password) {
        String query  = "SELECT * FROM tblUser WHERE (UserName='"+userName+"' AND Password='"+password+"') AND IsActived='true'";
        try {
            PreparedStatement ps = ConnectionFactory.getConnection().prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            rs.next();
            util.Constant.fullName = rs.getString("Name");
            util.Constant.role = rs.getBoolean("Role");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    @Override
    public void logout() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public User getUserByUserName(String userName) {
        User user = new User();
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "select * from tblUser where UserName = '" + userName + "' and IsActived = 'true'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                user.setUserID(rs.getInt("UserID"));
                user.setUserName(rs.getString("UserName"));
                user.setPassword(rs.getString("Password"));
                user.setRole(rs.getBoolean("Role"));
                user.setName(rs.getString("Name"));
                user.setBirthday(rs.getDate("Birthday"));
                user.setGender(rs.getBoolean("Gender"));
                user.setPhone(rs.getString("Phone"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }
}